package cn.ac.cintcm.synonymous;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import net.sf.json.JSONArray;

import oracle.jdbc.OraclePreparedStatement;

public class NameRecordDao {

	public static final String table = "syn_rec";

	private static final int BATCH_SIZE = 50;

	private Connection conn;

	private NameFieldDao categoryDao;

	private NameFieldDao sourceDao;

	public NameRecordDao(Connection conn, NameFieldDao categoryDao,
			NameFieldDao sourceDao) {
		this.conn = conn;
		this.categoryDao = categoryDao;
		this.sourceDao = sourceDao;
	}

	static void dropTable(Connection conn) {
		String sql1 = "drop table " + table;
		String sql2 = "drop sequence seq_" + table;
		Statement stmt;
		try {
			stmt = conn.createStatement();
			stmt.execute(sql1);
			stmt.execute(sql2);
			stmt.close();
		} catch (SQLException e) {
		}
	}

	static void createTable(Connection conn) throws SQLException {
		Statement stmt = conn.createStatement();
		StringBuilder sql = new StringBuilder();
		sql
				.append("create table "
						+ table
						+ " (id number primary key, name varchar2(100) not null, type varchar2(1) not null, source ");
		sql
				.append("number not null, category number not null, reference varchar2(100))");
		stmt.execute(sql.toString());
		sql.setLength(0);
		sql.append("create sequence seq_" + table
				+ " minvalue 1 start with 1 increment by 1");
		stmt.execute(sql.toString());
		sql.setLength(0);
		sql.append("create trigger trig_" + table + " before insert on "
				+ table + " for each row begin \n");
		sql.append("select seq_" + table + ".nextval into :new.ID from dual;");
		sql.append("end;");
		stmt.execute(sql.toString());
		stmt.close();
	}

	public List<NameRecord> getAll() throws SQLException {
		String sql = "select id, name, type, source, category, reference from "
				+ table;
		Statement stmt = conn.createStatement();
		ResultSet rset = stmt.executeQuery(sql);
		List<NameRecord> list = new ArrayList<NameRecord>();
		while (rset.next()) {
			NameRecord nr = new NameRecord();
			nr.setId(rset.getLong(1));
			nr.setName(rset.getString(2));
			nr.setType(rset.getString(3).charAt(0));
			nr.setSource(sourceDao.getNameField(rset.getShort(4)));
			nr.setCategory(categoryDao.getNameField(rset.getShort(5)));
			nr.setReference(rset.getString(6));
			list.add(nr);
		}
		rset.close();
		stmt.close();
		return list;
	}

	public void save(NameRecord nr) throws SQLException {
		PreparedStatement ps = conn.prepareStatement("insert into " + table
				+ " values(?, ?, ?, ?, ?, ?)");
		try {
			ps.setLong(1, nr.getId());
			ps.setString(2, nr.getName());
			ps.setString(3, nr.getType() + "");
			ps.setInt(4, nr.getSource().getId());
			ps.setInt(5, nr.getCategory().getId());
			ps.setString(6, nr.getReference());
			ps.executeUpdate();
		} catch (SQLException e) {
			ErrorData.write("data", nr.getName());
		}
		ps.close();
	}

	public void saveAll(List<NameRecord> list) throws SQLException {
		PreparedStatement ps = conn.prepareStatement("insert into " + table
				+ " values(?, ?, ?, ?, ?, ?)");
		((OraclePreparedStatement) ps).setExecuteBatch(BATCH_SIZE);
		for (NameRecord nr : list) {
            if (nr.getSource() == null || nr.getCategory() == null) {
                ErrorData.write("data", nr.getName() + " 分类或出处为空");
            } else {
    			try {
    				ps.setLong(1, nr.getId());
    				ps.setString(2, nr.getName());
    				ps.setString(3, nr.getType() + "");
    				ps.setInt(4, nr.getSource().getId());
    				ps.setInt(5, nr.getCategory().getId());
    				ps.setString(6, nr.getReference());
    				ps.executeUpdate();
    			} catch (SQLException e) {
    				ErrorData.write("data", nr.getName());
    			}
            }
		}
		try {
			ps.close();
		} catch (SQLException e) {
			ErrorData.write("system", e.getMessage());
		}
	}

	public QueryRecords getRecords(Choice selectedChoice1,
			Choice selectedChoice2, ArrayList selectedCategories1,
			ArrayList selectedCategories2, ArrayList selectedSources1,
			ArrayList selectedSources2) throws SQLException {
		StringBuilder sql = new StringBuilder();
		sql
				.append("select * from (select a.id ad, a.name an, a.reference ar, a.source ac, a.type ap, a.category ag, b.id bd, b.name bn, b.reference br, b.source bc, b.type bp, b.category bg from SYN_REC a, syn_rec b where  a.source != b.source and a.name=b.name) c where ");
		boolean hasValue = false;
		if (selectedChoice1 != null) {
			if (selectedChoice1.getId() != NameRecord.ALL) {
				sql.append(" ap='");
				sql.append(selectedChoice1.getId());
				sql.append("' ");
				hasValue = true;
			}
		}
		if (selectedChoice2 != null) {
			if (selectedChoice2.getId() != NameRecord.ALL) {
				if (hasValue) {
					sql.append(" and ");
				}
				sql.append(" bp='");
				sql.append(selectedChoice2.getId());
				sql.append("' ");
				hasValue = true;
			}
		}

		hasValue = buildSql(selectedCategories1, hasValue, "ag", sql);
		hasValue = buildSql(selectedCategories2, hasValue, "bg", sql);
		hasValue = buildSql(selectedSources1, hasValue, "ac", sql);
		hasValue = buildSql(selectedSources2, hasValue, "bc", sql);
		//System.out.println(sql.toString());
		Statement stmt = conn.createStatement();
		ResultSet rset = stmt.executeQuery(sql.toString());
		List<NameRecord[]> list = new ArrayList<NameRecord[]>();
		QueryRecords qr = new QueryRecords();
		Set<NameField> categories1 = new HashSet<NameField>();
		Set<NameField> categories2 = new HashSet<NameField>();
		while (rset.next()) {
			NameRecord[] nr = new NameRecord[2];
			NameRecord anr = new NameRecord();
			anr.setId(rset.getLong("ad"));
			anr.setName(rset.getString("an"));
			anr.setType(rset.getString("ap").charAt(0));
			anr.setSource(sourceDao.getNameField(rset.getShort("ac")));

			NameField anrCategory = categoryDao.getNameField(rset
					.getShort("ag"));
			anr.setCategory(anrCategory);
			categories1.add(anrCategory);

			anr.setReference(rset.getString("ar"));
			nr[0] = anr;

			NameRecord bnr = new NameRecord();
			bnr.setId(rset.getLong("bd"));
			bnr.setName(rset.getString("bn"));
			bnr.setType(rset.getString("bp").charAt(0));
			bnr.setSource(sourceDao.getNameField(rset.getShort("bc")));

			NameField bnrCategory = categoryDao.getNameField(rset
					.getShort("bg"));
			bnr.setCategory(bnrCategory);
			categories2.add(bnrCategory);

			bnr.setReference(rset.getString("br"));
			nr[1] = bnr;

			list.add(nr);
		}
		qr.setCategories1(categories1);
		qr.setItems(list);
		qr.setCategories2(categories2);
		rset.close();
		stmt.close();

		return qr;
	}

	private boolean buildSql(ArrayList list, boolean hasValue, String field,
			StringBuilder sql) {
		boolean added = false;
		if (list != null && list.size() > 0) {
			if (hasValue) {
				sql.append(" and ");
			}
			sql.append(field);
			sql.append(" in(");
			for (Object o : list) {
				sql.append(((NameField) o).getId());
				sql.append(",");
			}
			sql.deleteCharAt(sql.length() - 1);
			sql.append(") ");
			added = true;
		}
		return added || hasValue;
	}

	public List<OverView> getOutline() throws SQLException {
		String sql = "select count(*), source from " + table
				+ " group by source";
		Statement stmt = conn.createStatement();
		ResultSet rset = stmt.executeQuery(sql);
		List<OverView> list = new ArrayList<OverView>();
		while (rset.next()) {
			OverView nr = new OverView();
			short id = rset.getShort(2);
			nr.setId(id);
			nr.setName(sourceDao.getNameField(id).getName());
			nr.setCount(rset.getString(1));
			list.add(nr);
		}
		rset.close();
		stmt.close();
		return list;
	}

	public List<OverView> getCategoryOutline(short sourceId)
			throws SQLException {
		String sql = "select count(*), category from " + table
				+ " group by source, category having source=" + sourceId;
		System.out.println(sql);
		Statement stmt = conn.createStatement();
		ResultSet rset = stmt.executeQuery(sql);
		List<OverView> list = new ArrayList<OverView>();
		while (rset.next()) {
			OverView nr = new OverView();
			short id = rset.getShort(2);
			nr.setId(id);
			nr.setName(categoryDao.getNameField(id).getName());
			nr.setCount(rset.getString(1));
			list.add(nr);
		}
		rset.close();
		stmt.close();
		return list;
	}

	public ResultTable getComparison(JSONArray sou, JSONArray category, Integer state)
			throws SQLException {
		if(sou == null || sou.length()<2)
			return null;
		Map mapCategory = new HashMap();
		Map mapSource = new LinkedHashMap();
		
		this.initMapCategory(category, mapCategory);
		this.initMapSource(sou, mapSource);
		List listTitle = new ArrayList();
		String sql = this.structureSQL(mapSource, mapCategory, state,listTitle);
		
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery(sql);
        ResultTable resultTable = new ResultTable();

        resultTable.setHead(listTitle);
        int size = listTitle.size();
        
		try {
        while (rset.next()) {
        	List list = new ArrayList();
        	for(int i=1; i<=size ;i++)
        	{
        		
        		Object o = rset.getObject(i);
        		if(o != null)
        			list.add(o);
        		else list.add("");
        	};
        	resultTable.getData().add(list);
        }
        resultTable.setTableSize(resultTable.getData().size());
        rset.close();
        stmt.close();
        } catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultTable;
	}

	/**
	 * ��ʼ��Category ����ҳ����ѡ���� ���� key(���Դid[SOURCE]) value(��ѡ��id��CATEGORY��) ����map
	 * @param category
	 */
	private void initMapCategory(JSONArray category, Map mapCategory) {
		if (category != null && !category.isEmpty()) {
			int size = category.length();
			for (int i = 0; i < size; i++) {
				String sourceId = category.getJSONObject(i).getString(
						"sourceId");
				String categoryId = category.getJSONObject(i).getString(
						"category");
				if (sourceId != null
						&& !(sourceId = sourceId.trim()).equals("")) {
					if (categoryId != null
							&& !(categoryId = categoryId.trim()).equals("")) {
						if (mapCategory.containsKey(sourceId)) {
							categoryId += ("," + mapCategory.get(sourceId)
									.toString());
						}
						mapCategory.put(sourceId, categoryId);
					}
				}

			}
		}
	}

	/**
	 * ��ʼ��Category ����ҳ����ѡ�ڱ?���Դ�� ���� key(���Դid[SOURCE]) value(��ѡ�����ͣ�TYPE(��������)��) ����map
	 * @param category
	 */
	private void initMapSource(JSONArray sou, Map mapSource) {
		if (sou != null && !sou.isEmpty()) {
			int size = sou.length();
			for (int i = 0; i < size; i++) {
				String sourceId = sou.getJSONObject(i).getString("sourceId");
				String cmpType = sou.getJSONObject(i).getString("cmpType");
				if (sourceId != null
						&& !(sourceId = sourceId.trim()).equals("")) {
					if (cmpType != null
							&& !(cmpType = cmpType.trim()).equals("")) {
						mapSource.put(sourceId, cmpType);
					}
				}

			}
		}
	}

	/**
	 *   ������ѯsql һ���������
	 *    1:select �ֶ���ʾ����
	 *    2:������ѯ�ı�
	 *    3:������ѯ���
	 * @param mapSource
	 * @param mapCategory
	 * @param state
	 * @return
	 */
	private String structureSQL(Map mapSource, Map mapCategory, Integer state,List list) {
		if (mapSource.isEmpty())
			return null;
		
		/***
		 * ��һ�б�ͷ
		 */
		list.add("��ȫ��ͬ��");
		/**
		 * ��Ҫ��ʾ��Щ�ֶ�
		 */
		StringBuilder stringBuView = new StringBuilder(" ");
		/**
		 * ������
		 */
		StringBuilder stringBuTable = new StringBuilder(" ");
		/***
		 * ��ѯ���
		 */
		StringBuilder stringBuCon = new StringBuilder(" ");

		StringBuilder stringBuCon2 = new StringBuilder(" ");

		Iterator iterator = mapSource.keySet().iterator();
		int i = 1;
		String tem = "";
		String temRe = "";
		while (iterator.hasNext()) {
			int numName = i++;
			String temWhere = "a" + numName + ".name";
			String temWhereRe = "a" + numName + ".reference";
			String sourceId = (String) iterator.next();
			String cmpType = (String) mapSource.get(sourceId);

			if (numName != 1) {
				stringBuCon.append(" AND " + tem + "=" + temWhere);
				
				if(NameRecord.FORMAL ==cmpType.charAt(0))
					list.add(sourceDao.getNameField(Short.parseShort(sourceId)).getName()+" ����");
				else if(NameRecord.ALL ==cmpType.charAt(0))
				{
					list.add(sourceDao.getNameField(Short.parseShort(sourceId)).getName()+" ȫ��");
				}
				else
					list.add(sourceDao.getNameField(Short.parseShort(sourceId)).getName()+"����");
				
				stringBuView.append("," + temWhereRe);
				stringBuCon2
						.append(temRe +"=" + temWhereRe+" AND ");
				stringBuTable.append(" ,(select NVL(REFERENCE,' ') REFERENCE ,NAME from SYN_REC where source="
						+ sourceId );
				/***���ѡ��ȫ���Ļ���ȡ�����**/
				if(NameRecord.ALL !=cmpType.charAt(0))
					stringBuTable.append(" and type='" + cmpType + "'  ");
			} else {
				stringBuTable.append("(select NVL(REFERENCE,' ') REFERENCE ,NAME from SYN_REC where source="
						+ sourceId);
				/***���ѡ��ȫ���Ļ���ȡ�����**/
				if(NameRecord.ALL !=cmpType.charAt(0))
					stringBuTable.append(" and type='" + cmpType + "'  ");
				
				/***
				 * ����������ȡ����- ��֮
				 */
				stringBuView.append(temWhere);

				if(NameRecord.FORMAL ==cmpType.charAt(0))
				{
					list.add(sourceDao.getNameField(Short.parseShort(sourceId)).getName()+" ����");
				}else if(NameRecord.ALL ==cmpType.charAt(0))
				{
					list.add(sourceDao.getNameField(Short.parseShort(sourceId)).getName()+" ȫ��");
				}
				else
				{
					list.add(sourceDao.getNameField(Short.parseShort(sourceId)).getName()+" ����");
				}
				stringBuView.append(","+temWhereRe);
			}
			if (mapCategory.containsKey(sourceId))
				stringBuTable.append("and CATEGORY in("
						+ mapCategory.get(sourceId) + ")");
			stringBuTable.append(" ) a" + numName);
			tem = temWhere;
			temRe = temWhereRe;

		}

		String sql = "select distinct " + stringBuView + " from " + stringBuTable
				+ " where 1=1 " + stringBuCon;
		String stringBuCon2Tem = "";
		if(stringBuCon2 != null)
			stringBuCon2Tem = stringBuCon2.substring(0,stringBuCon2.lastIndexOf("AND"));
		
		if (state.intValue() == Parameters.STATIC_NOT)
			sql += " AND NOT( "+stringBuCon2Tem+")";
		if (state.intValue() == Parameters.STATIC_YES)
			sql += " AND "+ stringBuCon2Tem;
			
		return sql;
	}
}
